﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using UpdateStruct.Common;
using UpdateStruct.DBUtility;
namespace UpdateStruct.DBstruct
{
    /// <summary>
    ///     数据库结构解析类
    /// </summary>
    /// <remarks>
    ///     
    /// </remarks>
    public  class StructHelper
    {
         DbHelperSQLP  sqlhelper = null;
         DbHelperOleDb cfghelper = null;
         private string connstring = "Data Source=fpbaggio;Initial Catalog=newsiming;User ID=sa;Password=1";
        public StructHelper()
        {
            //sqlhelper = new DBUtility.DbHelperSQLP("Data Source=.;Initial Catalog=KingboxBZL;Persist Security Info=True;User ID=sa;Password=123");
            //sqlhelper = new DbHelperSQLP(UpdateStruct.DESEncrypt.Decrypt(new INIFile().IniReadValue("XCCN", "SQL")));
            //cfghelper = new DbHelperOleDb(UpdateStruct.DESEncrypt.Decrypt(new INIFile().IniReadValue("XCCN", "CFG")));

            sqlhelper = new DbHelperSQLP(connstring);

        }

        /// <summary>
        ///     获取所有表结构
        /// </summary>
        /// <returns>
        ///     A System.Data.DataSet value...
        /// </returns>
        public DataSet GetTableStruct()
        {
            string strsql = "SELECT * FROM S_S_DATA ";
            DataSet DS = sqlhelper.Query(strsql);
            return DS;
        }

        /// <summary>
        ///     重建表
        /// </summary>
        /// <param name="tablename" type="string">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <returns>
        ///     A bool value...
        /// </returns>
        public bool RebuiltTable(string tablename)
        {
            bool Brv;
            try
            {
                string tablestru = GetTableStruct(tablename.ToUpper());
                if (string.IsNullOrWhiteSpace(tablestru))
                {
                    throw new Exception("无对应的结构定义");
                }

                //先建立和S_S_Data 一样的表，然后把结构解析进去
                List<string> sList = StringPlus.GetStrlist(tablestru, "\r\n");
                
                //从第一行取的表名和中文名
                if (sList.Count < 0)
                {
                    throw new Exception("结构解析失败！");
                }
                string tablecname = sList[0].Split(',')[1] ;
                string fStr=string.Empty ;
                string[] filed;
                string strFileType = string.Empty;
                string strFileLen = string.Empty;
                string strFileShowLen = string.Empty;

                StringBuilder sb = new StringBuilder();
                for (int i = 1; i <= sList.Count - 1; i++)
                { 
                    fStr=sList[i].ToString();
                    //前面是 ' 注释，不处理
                    if (fStr.Substring(0,1).Equals("'"))
                    {
                        
                    }
                    //前面SUB 调子结构
                    else if (fStr.Substring(0,3).ToUpper().Equals("SUB"))
                    {
                       sb.AppendLine( AnalysisSubStruToSql(fStr,tablename,tablecname,"S_S_DATA"));
                    }
                    else //正常字段解析
                    {
                        fStr += ",,,,,";
                        filed = StringPlus.GetStrArray(fStr,',');

                        strFileType = GetFieldTypeToChina(filed[2].Substring(0, 1));
                        strFileLen =GetFieldLen(strFileType, StringPlus.GetStrArray(filed[2].Substring(1)+"////", '/')[0]);
                        strFileShowLen = StringPlus.GetStrArray(filed[2].Substring(1) + "////", '/')[1];

                        if (string.IsNullOrEmpty(strFileType))
                            throw new Exception("字段" + filed[0] + "数据类型定义不正确！");

                        if (filed[3].Length == 0) filed[3] = "0";
                        if (filed[4].Length == 0) filed[4] = "0";


                        sb.AppendLine(" INSERT INTO S_S_DATA( ");
                        sb.AppendLine(" TableName,TableCName,");
                        sb.AppendLine(" FieldName,FieldType,FieldLen,FieldCap,FieldCapDef,CanUse,UseOrNot,lFromSub,");
                        sb.AppendLine(" FromSubT,FromSubT2,ShowLen,DiaoSubT) ");
                        sb.AppendFormat(" SELECT '{0}','{1}',",tablename,tablecname);
                        sb.AppendFormat(" '{0}' FIELDNAME,'{1}' AS FIELDTYPE,'{2}' as FIELDLEN, '{0}' as FIELDCAP,'{0}' as FIELDCAPDEF,'{3}' as CANUSE,'{4}' as USEORNOT,0 as LFROMSUB,",
                            filed[0], strFileType, strFileLen,filed[3],filed[4]);
                        sb.AppendFormat(" '' AS FROMSUBT,'' AS FROMSUBT2,'{0}' as SHOWLEN,'' AS DIAOSUBT ", strFileShowLen);
                    }

                }

                //解析出SQL语句后，需要将原先的数据字段信息备份一下
                string strTmpStrutable = tablename + "_S_S_Data_bak";
                sqlhelper.DropTable(strTmpStrutable);
                sqlhelper.ExecuteSql(string.Format("SELECT * INTO {0} FROM S_S_DATA WHERE TABLENAME='{1}'", strTmpStrutable,tablename));

                sqlhelper.ExecuteSql(string.Format("DELETE FROM S_S_DATA WHERE TABLENAME='{0}'",tablename));
                sqlhelper.ExecuteSql(sb.ToString());


                //备份原表数据
                string strTmpDatatable = tablename + "_bak";
                sqlhelper.DropTable(strTmpDatatable);
                Backuptable(tablename, strTmpDatatable);
                //重建表结构
                string StrsqlTable = GetTableSQL(tablename);
                sqlhelper.ExecuteSql(StrsqlTable);

                //再将数据写回到原表中
                sqlhelper.CopyTable(strTmpDatatable, tablename);

                
                
                #region//用原先备份的数据字段信息更新到新的数据字典
                sb.Clear();
                sb.AppendLine(" UPDATE S_S_DATA ");
                sb.AppendLine(" SET S_S_DATA.USEORNOT=S_S_SUBD.USEORNOT,S_S_DATA.SHOWLEN=S_S_SUBD.SHOWLEN, ");
                sb.AppendLine(" S_S_DATA.FIELDCAP=S_S_SUBD.FIELDCAP,S_S_DATA.FIELDCAPDEF=S_S_SUBD.FIELDCAPDEF  ");
                sb.AppendFormat(" FROM S_S_DATA,S_S_SUBD WHERE S_S_DATA.TableName='{0}'",tablename);
                sb.AppendLine(" AND S_S_DATA.LFROMSUB=1 AND (RTRIM(S_S_DATA.FROMSUBT2)=RTRIM(S_S_SUBD.TABLENAME)) ");
                sb.AppendLine(" AND (RTRIM(S_S_DATA.FIELDNAME)=RTRIM(S_S_SUBD.FIELDNAME));");

                sb.AppendFormat("UPDATE S_S_DATA SET SHOWLEN=FIELDLEN WHERE TableName='{0}' AND SHOWLEN<=0 OR SHOWLEN IS NULL; ", tablename);
                sb.AppendFormat("UPDATE S_S_DATA SET PRINTLEN=SHOWLEN WHERE TableName='{0}'; ", tablename); 
                
                sb.AppendFormat("UPDATE S_S_DATA SET PRINTLEN=-1 WHERE TableName='{0}' AND LEFT(FIELDNAME,5)='GOOD_' AND RIGHT(RTRIM(FIELDNAME),2)>'01';", tablename);

                sb.AppendFormat("UPDATE S_S_DATA SET XHB='头尾' WHERE TableName='{0}' AND RIGHT(RTRIM(TABLENAME),2)='_H'", tablename);
                sb.AppendFormat("UPDATE S_S_DATA SET XHB='表体' WHERE TableName='{0}' AND RIGHT(RTRIM(TABLENAME),2)<>'_H'", tablename);

                sb.AppendFormat("UPDATE S_S_DATA SET S_S_DATA.FIELDCAP={0}.FIELDCAP,S_S_DATA.USEORNOT={0}.USEORNOT, ", strTmpStrutable);
                sb.AppendFormat("S_S_DATA.LFROMSUB={0}.LFROMSUB FROM {0}  ", strTmpStrutable);
                sb.AppendFormat("    INNER JOIN S_S_DATA ON S_S_DATA.TABLENAME={0}.TABLENAME  ", strTmpStrutable);
                sb.AppendFormat("        AND S_S_DATA.FIELDNAME={0}.FIELDNAME ", strTmpStrutable);
                sqlhelper.ExecuteSql(sb.ToString());

                #endregion

                //删除备份表
                sqlhelper.DropTable(strTmpDatatable);
                sqlhelper.DropTable(strTmpStrutable);
                Brv = true;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Brv = false;
            }
            return Brv ;
        }
        /// <summary>
        ///     分析并重建子结构
        /// </summary>
        /// <param name="subname" type="string">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <returns>
        ///     A bool value...
        /// </returns>
        public bool ReBuiltSub(string subname)
        { 
            bool Brv=false;
            try
            {
                string tablestru = GetTableStruct(subname.ToUpper());
                if (string.IsNullOrWhiteSpace(tablestru))
                {
                    throw new Exception("无对应的结构定义");
                }

                //先建立和S_S_Data 一样的表，然后把结构解析进去
                List<string> sList = StringPlus.GetStrlist(tablestru, "\r\n");

                //从第一行取的表名和中文名
                if (sList.Count < 0)
                {
                    throw new Exception("结构解析失败！");
                }
                string subcname = sList[0].Split(',')[1];
                string fStr = string.Empty;
                string[] filed;
                string strFileType = string.Empty;
                string strFileLen = string.Empty;
                string strFileShowLen = string.Empty;

                StringBuilder sb = new StringBuilder();
                for (int i = 1; i <= sList.Count - 1; i++)
                {
                    fStr = sList[i].ToString();
                    //前面是 ' 注释，不处理
                    if (fStr.Substring(0, 1).Equals("'"))
                    {

                    }
                    //前面SUB 调子结构
                    else if (fStr.Substring(0, 3).Equals("SUB"))
                    {
                        sb.AppendLine(AnalysisSubStruToSql(fStr, subname, subcname,"S_S_SUBD"));
                    }
                    else //正常字段解析
                    {
                        fStr += ",,,,,";
                        filed = StringPlus.GetStrArray(fStr, ',');

                        strFileType = GetFieldTypeToChina(filed[2].Substring(0, 1));
                        strFileLen = GetFieldLen(strFileType, StringPlus.GetStrArray(filed[2].Substring(1) + "////", '/')[0]);
                        strFileShowLen = StringPlus.GetStrArray(filed[2].Substring(1) + "////", '/')[1];

                        if (string.IsNullOrEmpty(strFileType))
                            throw new Exception("字段" + filed[0] + "数据类型定义不正确！");

                        if (filed[3].Length == 0) filed[3] = "0";
                        if (filed[4].Length == 0) filed[4] = "0";


                        sb.AppendLine(" INSERT INTO S_S_SUBD( ");
                        sb.AppendLine(" TableName,TableCName,");
                        sb.AppendLine(" FieldName,FieldType,FieldLen,FieldCap,FieldCapDef,CanUse,UseOrNot,lFromSub,");
                        sb.AppendLine(" FromSubT,FromSubT2,ShowLen,DiaoSubT) ");
                        sb.AppendFormat(" SELECT '{0}','{1}',", subname, subcname);
                        sb.AppendFormat(" '{0}' FIELDNAME,'{1}' AS FIELDTYPE,'{2}' as FIELDLEN, '{0}' as FIELDCAP,'{0}' as FIELDCAPDEF,'{3}' as CANUSE,'{4}' as USEORNOT,0 as LFROMSUB,",
                            filed[0], strFileType, strFileLen, filed[3], filed[4]);
                        sb.AppendFormat(" '' AS FROMSUBT,'' AS FROMSUBT2,'{0}' as SHOWLEN,'' AS DIAOSUBT ", strFileShowLen);
                    }

                }


                sqlhelper.ExecuteSql(string.Format("DELETE FROM S_S_SUBD WHERE TABLENAME='{0}'", subname));
                sqlhelper.ExecuteSql(sb.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Brv = false;
            }
            return Brv;
        }
        /// <summary>
        /// 将数据结构保存到数据表中
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="tableStruct"></param>
        /// <returns></returns>
        public bool SaveToDB(string tablename,string tablecname, string tableStruct)
        {
            string strsql = "";
            strsql +=string.Format( "IF EXISTS (SELECT * FROM S_S_TABLE WHERE TABLENAME='{0}')",tablename) ;
            strsql += string.Format("UPDATE S_S_TABLE SET STRUCT='{0}',UPDATETIME=GETDATE() ",StringPlus.FilterStr( tableStruct));
            strsql += string.Format(" WHERE TABLENAME='{0}'",tablename);
            strsql += " else ";
            strsql += "INSERT INTO S_S_TABLE (TABLENAME,TABLECNAME,STRUCT,UPDATETIME) ";
            strsql += string.Format(" SELECT '{0}','{1}','{2}',GETDATE()", tablename, tablecname, StringPlus.FilterStr( tableStruct));

            sqlhelper.ExecuteSql(strsql);
            return true;
        }

        public bool SaveToDB(string tableStruct)
        {
            //解析表名
            //先建立和S_S_Data 一样的表，然后把结构解析进去
            List<string> sList = StringPlus.GetStrlist(tableStruct, "\r\n");

            //从第一行取的表名和中文名
            if (sList.Count < 0)
            {
                throw new Exception("结构解析失败！");
            }
            string tname = sList[0].Split(',')[0];
            string tcname = sList[0].Split(',')[1];
            return SaveToDB(tname, tcname, tableStruct);

        }

        public bool SaveToXml()
        {
            DataSet ds = sqlhelper.Query("SELECT TABLENAME,TABLECNAME,STRUCT FROM S_S_TABLE");
            if (ds != null || ds.Tables.Count > 0)
            {
                foreach (DataRow mDr in dataSet.Tables[0].Rows)
                {
                    foreach (DataColumn mDc in dataSet.Tables[0].Columns)
                    {
                        Console.WriteLine(mDr[mDc].ToString());
                    }
                }
                strstru = ds.Tables[0].Rows[0][0].ToString();
            }

        }
        public bool SaveToXml(string tablename, string tablecname, string tableStruct)
        { 
        
        }
        #region 私有方法
        /// <summary>
        ///     获取单据表结构
        /// </summary>
        /// <param name="tablename" type="string">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <returns>
        ///     A string value...
        /// </returns>
        private String GetTableStruct(string tablename)
        {   
            
            string strstru=string.Empty ;
            //从access数据库中读所有的表脚本回来
            DataSet ds = sqlhelper.Query(string.Format( "SELECT STRUCT FROM S_S_TABLE where TABLENAME='{0}' ",tablename));
            if (ds != null || ds.Tables.Count > 0)
            {
                strstru = ds.Tables[0].Rows[0][0].ToString();
            }
            List<string> sList = StringPlus.GetStrlist(strstru, "*");
            foreach (string t in sList)
            {
                if (t.IndexOf(tablename) == 0)
                {
                    strstru = t.ToString();
                    break; 
                }

            }


            #region 测试数据 
            //strstru += "INFO_WORK,员工信息" + "\r\n";
            //strstru += "SUB:BASEWORK" + "\r\n";
            //strstru += "SUB:BASETREEWORK/INFO_TREEWORK" + "\r\n";
            //strstru += "SUB:BASETGWEI/INFO_TREEGWEI" + "\r\n";
            //strstru += "SUB:BASETPART/INFO_TREEPART" + "\r\n";
            //strstru += "SEX,性别,C4/2,1,1" + "\r\n";
            //strstru += "BIRTHDAY,出生日期,D" + "\r\n";
            //strstru += "XYEAR,年龄,L" + "\r\n";
            //strstru += "DEPT,核算部门,C20/6" + "\r\n";
            //strstru += "POST,岗位,C20/6" + "\r\n";
            //strstru += "DUTY,职务,C20/6" + "\r\n";
            //strstru += "XUELI,文化程度,C20/6" + "\r\n";
            //strstru += "ZHICHENG,技术职称,C20/6" + "\r\n";
            //strstru += "ISFHR,是否复核人,B,1,1" + "\r\n";
            //strstru += "XPASS,拣货密码,M" + "\r\n";
            #endregion


            return strstru;
        }

        private string GetSubStruct(string subname)
        {
            string strstru = string.Empty;
            //从access数据库中读所有的表脚本回来
            DataSet ds = cfghelper.Query("SELECT SysNote FROM SysMemo where SysFlag='SubStruBZL' ");
            if (ds != null || ds.Tables.Count > 0)
            {
                strstru = ds.Tables[0].Rows[0][0].ToString();
            }
            List<string> sList = StringPlus.GetStrlist(strstru, "*");
            foreach (string t in sList)
            {
                if (t.IndexOf(subname) == 0)
                {
                    strstru = t.ToString();
                    break;
                }

            }
            return strstru;
        }
        private static bool FindResult(String s)
        {
            if ((s.Length > 5) &&
                (s.Substring(s.Length - 6).ToLower() == "saurus"))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///    解析子结构
        /// </summary>
        /// <param name="strSub" type="string">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <returns>
        ///     A string value...
        /// </returns>
        private string AnalysisSubStruToSql(string strSub,string tablename,string tablecname,string Tartable)
        { 
            //SUB:BASESUPP
            //SUB:BASEWORK/INFO_WORK
            if (string.IsNullOrWhiteSpace(Tartable))
            {
                Tartable = "S_S_DATA";
            }
            StringBuilder sb = new StringBuilder();
            string subname =string.Empty ;
            string subdiao=string.Empty ;
            strSub=strSub.Replace("SUB:","")+"////";
            subname=strSub.Split('/')[0];
            subdiao=strSub.Split('/')[1];
            DataSet ds = sqlhelper.Query(string.Format("SELECT * FROM S_S_TABLE WHERE TABLENAME='{0}'", subname));
            if (ds != null || ds.Tables.Count > 0)
            {
                sb.AppendFormat(" INSERT INTO {0}( ",Tartable);
                sb.AppendLine(" TableName,TableCName,");
                sb.AppendLine(" FieldName,FieldType,FieldLen,FieldCap,FieldCapDef,CanUse,UseOrNot,lFromSub,");
                sb.AppendLine(" FromSubT,FromSubT2,ShowLen,DiaoSubT) ");
                // VALUES('INFO_TEST','测试表','TESTMEMO','备注',4,'备注','备注',0,0,0,'','',0,'')
                sb.AppendFormat(" SELECT '{0}','{1}',",tablename,tablecname);
                sb.AppendLine(" FIELDNAME,FIELDTYPE,FIELDLEN,FIELDCAP,FIELDCAPDEF,CANUSE,USEORNOT,1 as LFROMSUB,");
                sb.AppendFormat(" TABLECNAME AS FROMSUBT,TABLENAME AS FROMSUBT2,SHOWLEN,'{0}' AS DIAOSUBT ", subdiao);
                sb.AppendFormat(" FROM S_S_SUBD WHERE TABLENAME='{0}' ",subname);
            }



            return sb.ToString();
        }

        /// <summary>
        ///     从S_S_Data表中拼成对应的Sql语句
        /// </summary>
        /// <param name="tablename" type="string">
        ///     <para>
        ///         
        ///     </para>
        /// </param>
        /// <returns>
        ///     A string value...
        /// </returns>
        private string GetTableSQL(string tablename)
        { 
            StringBuilder sb=new StringBuilder ();
            sb.AppendFormat (" Create Table {0} ( ",tablename);
            DataSet ds= sqlhelper.Query("SELECT FIELDNAME,FIELDTYPE,FIELDLEN  FROM s_s_data WHERE TABLENAME='" + tablename  + "' ORDER BY ID ");
            if (ds != null || ds.Tables.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows )
                {
                    //[WORKCODE] NVARCHAR(8)
                    sb.AppendFormat(" [{0}] {1},", dr["FIELDNAME"], GetFieldTypeSqlFromChina(dr["FIELDTYPE"].ToString(), dr["FIELDLEN"].ToString()));
                }
            }
            sb.AppendLine(" ID INT IDENTITY )");

            return sb.ToString();
        }

        private string GetFieldTypeSqlFromChina(string fiedlftype,string fieldlen)
        { 
            string fieldsql=string.Empty;
            switch (fiedlftype)
            {
                case "字符":
                    fieldsql = "NVARCHAR("+ fieldlen +")";
                    break;
                case "是否":
                    fieldsql = "BIT DEFAULT 0";
                    break;
                case "备注":
                    fieldsql="NVARCHAR(3888)";
                    break;
                case "图像":
                    fieldsql="IMAGE";
                    break;
                case "整数":
                    fieldsql="INT DEFAULT 0";
                    break;
                case "实数":
                    fieldsql="NUMERIC(18,6) DEFAULT 0";
                    break;
                case "日期":
                    fieldsql="DATETIME";
                    break;
            }

            return fieldsql;
        }

        private string GetFieldTypeToChina(string fieldftype)
        {
            string fieldName = string.Empty;
            switch (fieldftype)
            {
                case "C":
                    fieldName = "字符";
                    break;
                case "B":
                    fieldName = "是否";
                    break;
                case "M":
                    fieldName = "备注";
                    break;
                case "I":
                    fieldName = "图像";
                    break;
                case "L":
                    fieldName = "整数";
                    break;
                case "N":
                    fieldName = "实数";
                    break;
                case "D":
                    fieldName = "日期";
                    break;
            }

            return fieldName;
        }
        /// <summary>
///     将数据表备份到另外一张表中
/// </summary>
/// <param name="sTablename" type="string">
///     <para>
///         
///     </para>
/// </param>
/// <param name="ttablename" type="string">
///     <para>
///         
///     </para>
/// </param>
/// <returns>
///     A bool value...
/// </returns>
        private bool Backuptable(string  sTablename, string ttablename)
        {
            if (sqlhelper.TabExists(sTablename))
            {
                string strsql = "EXEC sp_rename '"+ sTablename + "', '"+ ttablename +"'";

                sqlhelper.ExecuteSql(strsql); 
            }
            return true ;
        }


        private string GetFieldLen(string fieldtype,string deflen)
        {
            string fieldName = string.Empty;
            switch (fieldtype)
            {
                case "字符":
                    fieldName = deflen;
                    break;
                case "是否":
                    fieldName = "2";
                    break;
                case "备注":
                    fieldName = "4";
                    break;
                case "图像":
                    fieldName = "255";
                    break;
                case "整数":
                    fieldName = "6";
                    break;
                case "实数":
                    fieldName = "9";
                    break;
                case "日期":
                    fieldName = "12";
                    break;
            }

            return fieldName;
        }

        #endregion
    }
}
